import mysql.connector
import json
import os
from datetime import datetime
from collections import defaultdict
from dotenv import load_dotenv
from decimal import Decimal, getcontext

def get_db_connection():
    conn = None # 初始化 conn 为 None
    load_dotenv()
    try:
        conn = mysql.connector.connect(
            host=os.getenv("DB_HOST", "localhost"), # 数据库服务器地址
            user=os.getenv("DB_USER", "root"), # 数据库用户名
            password=os.getenv("DB_PASSWORD", "password"), # 数据库密码
            database=os.getenv("DB_NAME", "aidemo"), # 数据库名称
            port=os.getenv("DB_PORT", "3306")
        )
        print(f"DEBUG: 数据库连接成功建立到: aidemo") # 添加调试信息
        return conn # <-- 关键！返回建立好的连接对象
    except mysql.connector.Error as e:
        print(f"ERROR: 建立数据库连接失败或数据库操作错误: {e}")
        return None # 连接失败时返回 None
    except Exception as e:
        print(f"ERROR: 建立数据库连接时发生未知错误: {e}")
        return None # 捕获其他未知错误并返回 None
    
def query_stock(keywords: str) -> str:
    """
    Queries product records where product name, type, or description contain the keywords.
    Returns the matching product records in JSON format.

    Args:
        keywords (str): The keywords to search for in product name, type, or description.

    Returns:
        str: A JSON string representing the of list matching product records.
            Returns an empty JSON array if no matches are found or on error.
    """
    conn = None
    result_data = []

    try:
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)  # 使用字典游标，直接返回字典格式的数据

        # Construct the SQL query with case-insensitive search
        # Using LIKE with %keywords% for partial matching
        # In, MySQL case-insensitive comparison is the default behavior for most collations
        query = """
            SELECT
                product_id,
                product_name,
                product_type,
                specification,
                price,
                stock_quantity,
                description
            FROM
                products
            WHERE
                product_name LIKE %s OR
                product_type LIKE %s OR
                description LIKE %s;
        """
        # Prepare the keywords for LIKE query
        search_term = f"%{keywords}%"

        cursor.execute(query, (search_term, search_term, search_term))
        rows = cursor.fetchall()

        for row in rows:
            # cursor.fetchall() already returns a list of dictionaries
            result_data.append(row)

    except mysql.connector.Error as e:
        print(f"Database error during stock query: {e}")
        # Optionally, you could log this error or raise it
    except Exception as e:
        print(f"An unexpected error occurred: {e}")
    finally:
        if conn:
            conn.close()

    return json.dumps(result_data, ensure_ascii=False, indent=4)

def create_order(
    purchaser: str, 
    products_in_order: list,
    expected_delivery_time: str) -> dict:
    """
    Creates a new order in the database, including order details and stock updates.
    The order_id will be in YYYYMMDDHHMMSS format based on the current timestamp.

    Args:
        purchaser (str): The name of the purchasing entity (e.g., '上海白金酒店管理有限公司').
        products_in_order (list): A list of dictionaries, where each dictionary
                                  represents a product in the order and contains:
                                  - 'product_id' (str): The ID of the product.
                                  - 'quantity' (int): The quantity ordered.
                                  Example: [{'product_id': '001', 'quantity': 10}, ...]

    Returns:
        dict: A dictionary containing the order status and details:
              - 'success' (bool): True if the order was created successfully, False otherwise.
              - 'order_id' (str, optional): The ID of the created order if successful.
              - 'message' (str): A message describing the outcome (e.g., success, error, insufficient stock).
              - 'total_amount' (float, optional): The calculated total amount of the order.
    """
    conn = None
    
    # Generate timestamp for order_id and order_time
    current_time = datetime.now()
    order_id = current_time.strftime('%Y%m%d%H%M%S')+ f"{current_time.microsecond:03d}"
    order_time = current_time.strftime('%Y-%m-%d %H:%M:%S')

    total_amount = Decimal('0.0')  # Use Decimal for total_amount
    response = {'success': False, 'message': 'Unknown error occurred.'}

    try:
        conn = get_db_connection()
        cursor = conn.cursor()

        # Start a transaction
        conn.start_transaction()

        # 1. Validate Product Availability and Calculate Total Amount
        detailed_products = []
        for item in products_in_order:
            product_id = item.get('product_id')
            quantity = item.get('quantity')

            if not product_id or not isinstance(quantity, int) or quantity <= 0:
                raise ValueError(f"Invalid product item: {item}. Must have 'product_id' and a positive 'quantity'.")

            # Fetch product details and check stock
            cursor.execute("SELECT product_name, price, stock_quantity FROM products WHERE product_id = %s", (product_id,))
            product_info = cursor.fetchone()

            if not product_info:
                raise ValueError(f"Product with ID '{product_id}' not found.")

            product_name, price, stock_quantity = product_info

            if stock_quantity < quantity:
                raise ValueError(f"Insufficient stock for product '{product_name}' (ID: {product_id}). Available: {stock_quantity}, Requested: {quantity}.")

            item_amount = Decimal(price) * quantity  # Use Decimal for item_amount
            total_amount += item_amount
            detailed_products.append({
                'product_id': product_id,
                'quantity': quantity,
                'price': price,
                'amount': item_amount
            })

        # 2. Insert into 'orders' table
        cursor.execute("""
                INSERT INTO orders (order_id, purchaser, total_amount, order_time, is_settled, expected_delivery_time)
                VALUES (%s, %s, %s, %s, %s, %s);
            """, (order_id, purchaser, total_amount, order_time, 0, expected_delivery_time))

        # 3. Insert into 'order_details' table and Update Stock
        for i, item in enumerate(detailed_products):
            # For detail_id, we can append a sequence number to the order_id
            # This makes detail_id unique within the order and descriptive
            detail_id = f"{order_id}-{i+1}"
            cursor.execute("""
                INSERT INTO order_details (detail_id, order_id, product_id, quantity, price, amount)
                VALUES (%s, %s, %s, %s, %s, %s);
            """, (detail_id, order_id, item['product_id'], item['quantity'], item['price'], item['amount']))

            # Update stock quantity
            cursor.execute("""
                UPDATE products
                SET stock_quantity = stock_quantity - %s
                WHERE product_id = %s;
            """, (item['quantity'], item['product_id']))

        # Commit the transaction if all operations are successful
        conn.commit()
        response['success'] = True
        response['order_id'] = order_id
        response['message'] = 'Order created successfully.'
        response['total_amount'] = float(total_amount)  # Convert Decimal to float for the response

    except ValueError as ve:
        # Rollback on business logic errors (e.g., insufficient stock, invalid product)
        if conn:
            conn.rollback()
        response['message'] = f"Order creation failed: {ve}"
    except mysql.connector.Error as e:
        # Rollback on database errors
        if conn:
            conn.rollback()
        response['message'] = f"Database error during order creation: {e}"
    except Exception as e:
        # Rollback on any other unexpected errors
        if conn:
            conn.rollback()
        response['message'] = f"An unexpected error occurred during order creation: {e}"
    finally:
        if conn:
            conn.close()

    return response

def generate_unsettled_statement(purchaser_name: str) -> str:
    """
    Generates a settlement statement for unsettled orders of a specific purchaser.
    Includes purchasing company name, unsettled order details,
    complete product information, and complete order information.

    Args:
        purchaser_name (str): The name of the purchasing company to query for.

    Returns:
        str: A JSON string representing the settlement statement for the specified purchaser.
             Returns an empty JSON array if no unsettled orders are found for this purchaser or on error.
    """
    conn = None
    # Use a regular dictionary, as we are only expecting one purchaser's data
    # The structure will still be a list for consistency with previous outputs.
    statement_data = defaultdict(lambda: {"purchaser_name": "", "unsettled_orders": defaultdict(lambda: {"order_info": {}, "order_details": []})})

    try:
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)  # Use dictionary cursor to get results as dictionaries

        # SQL query to join orders, order_details, and products tables
        # Filter for orders where is_settled = 0 (false) AND for the specific purchaser
        query = """
            SELECT
                o.order_id,
                o.purchaser,
                o.total_amount,
                o.order_time,
                o.delivery_time,
                o.is_settled,
                o.settlement_time,
                o.expected_delivery_time,
                od.detail_id,
                od.quantity AS ordered_quantity,
                od.price AS ordered_price,
                od.amount AS ordered_amount,
                p.product_id,
                p.product_name,
                p.product_type,
                p.specification,
                p.price AS current_product_price,
                p.stock_quantity,
                p.description
            FROM
                orders AS o
            JOIN
                order_details AS od ON o.order_id = od.order_id
            JOIN
                products AS p ON od.product_id = p.product_id
            WHERE
                o.is_settled = 0 AND o.purchaser = %s
            ORDER BY
                o.purchaser, o.order_time, od.detail_id;
        """
        cursor.execute(query, (purchaser_name,))  # Pass purchaser_name as a parameter
        rows = cursor.fetchall()

        if not rows:
            return json.dumps([], ensure_ascii=False, indent=4)  # Return empty array if no unsettled orders for this purchaser

        for row in rows:
            purchaser = row['purchaser']
            order_id = row['order_id']

            statement_data[purchaser]["purchaser_name"] = purchaser

            # Populate order_info
            if not statement_data[purchaser]["unsettled_orders"][order_id]["order_info"]:
                statement_data[purchaser]["unsettled_orders"][order_id]["order_info"] = {
                    "order_id": row['order_id'],
                    "total_amount": row['total_amount'],
                    "order_time": row['order_time'],
                    "delivery_time": row['delivery_time'],
                    "is_settled": bool(row['is_settled']),
                    "settlement_time": row['settlement_time'],
                    "expected_delivery_time": row['expected_delivery_time']
                }

            # Populate product_info for the detail
            product_info = {
                "product_id": row['product_id'],
                "product_name": row['product_name'],
                "product_type": row['product_type'],
                "specification": row['specification'],
                "current_price": row['current_product_price'],
                "stock_quantity": row['stock_quantity'],
                "description": row['description']
            }

            # Populate order_detail
            order_detail = {
                "detail_id": row['detail_id'],
                "quantity": row['ordered_quantity'],
                "price_at_order": row['ordered_price'],
                "amount": row['ordered_amount'],
                "product_info": product_info
            }

            statement_data[purchaser]["unsettled_orders"][order_id]["order_details"].append_detail(order_detail)

        # Restructure the defaultdict into the final list of dictionaries
        final_statement = []
        for purchaser_key, purchaser_data in statement_data.items():
            purchaser_entry = {
                "purchurer_name": purchaser_data["purchaser_name"],
                "unsettled_orders": []
            }
            for order_key, order_data in purchaser_data["unsettled_orders"].items():
                purchaser_entry["unsettled_orders"].append(order_data)
            final_statement.append(purchaser_entry)

    except mysql.connector.Error as e:
        print(f"Database error during settlement statement generation: {e}")
        return json.dumps([], ensure_ascii=False, indent=4)
    except Exception as e:
        print(f"An unexpected error occurred: {e}")
        return json.dumps([], ensure_ascii=False, indent=4)
    finally:
        if conn:
            conn.close()

    return json.dumps(final_statement, ensure_ascii=False, indent=4)

def sign_order(order_id: str) -> dict:
    """
    更新订单状态为已签收 (记录签收时间)。
    """
    conn = None
    response = {'success': False, 'message': 'Unknown error occurred.'}
    current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')

    try:
        conn = get_db_connection()        
        cursor = conn.cursor()

        # 检查订单是否存在
        cursor.execute("SELECT order_id, delivery_time FROM orders WHERE order_id = %s", (order_id,))
        existing_order = cursor.fetchone()
        if not existing_order:
            response['message'] = f"Order ID '{order_id}' not found."
            return response

        # 如果已经有签收时间，则认为已签收
        if existing_order[1] is not None: # Check delivery_time
            response['message'] = f"Order ID '{order_id}' has already been signed."
            response['success'] = True # 认为是成功，因为目标状态已达成
            return response

        # 更新订单的 delivery_time
        cursor.execute("""
            UPDATE orders
            SET delivery_time = %s
            WHERE order_id = %s;
        """, (current_time, order_id))

        conn.commit()
        response['success'] = True
        response['message'] = f"Order ID '{order_id}' successfully signed."

    except mysql.connector.Error as e:
        if conn: conn.rollback()
        response['message'] = f"Database error during order signing: {e}"
    except Exception as e:
        if conn: conn.rollback()
        response['message'] = f"An unexpected error occurred during order signing: {e}"
    finally:
        if conn:
            conn.close()

    return response

def settle_order(order_id: str) -> dict:
    """
    将指定订单标记为已结算。只有已签收但未结算的订单可以进行结算。
    """
    conn = None
    response = {'success': False, 'message': 'Unknown error occurred.'}
    current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')

    try:
        conn = get_db_connection()
        cursor = conn.cursor()

        # 检查订单是否存在及其当前状态
        cursor.execute("SELECT delivery_time, is_settled FROM orders WHERE order_id = %s", (order_id,))
        order_status = cursor.fetchone()

        if not order_status:
            response['message'] = f"Order ID '{order_id}' not found."
            return response

        delivery_time, is_settled = order_status

        if delivery_time is None:
            response['message'] = f"Order ID '{order_id}' has not been signed yet and cannot be settled."
            return response

        if is_settled == 1:
            response['message'] = f"Order ID '{order_id}' has already been settled."
            response['success'] = True  # 认为成功，因为目标状态已达成
            return response

        # 更新订单的 is_settled 状态和 settlement_time
        cursor.execute("""
            UPDATE orders
            SET is_settled = 1, settlement_time = %s
            WHERE order_id = %s;
        """, (current_time, order_id))

        conn.commit()
        response['success'] = True
        response['message'] = f"Order ID '{order_id}' successfully marked as settled."

    except mysql.connector.Error as e:
        if conn: conn.rollback()
        response['message'] = f"Database error during order settlement: {e}"
    except Exception as e:
        if conn: conn.rollback()
        response['message'] = f"An unexpected error occurred during order settlement: {e}"
    finally:
        if conn           :
            conn.close()

    return response

def query_orders(
    purchaser: str,
    order_time_min: str = None, # 格式: YYYY-MM-DD HH:MM:SS
    order_time_max: str = None, # 格式: YYYY-MM-DD HH:MM:SS
    is_signed: bool = None,
    is_settled: bool = None
) -> dict:
    """
    根据多个条件查询订单信息，包括订单明细和商品完整信息。
    
    Args:
        purchaser (str): 采购方公司名称 (必选)。
        order_time_min (str, optional): 下单时间范围最小值 (YYYY-MM-DD HH:MM:SS)。
        order_time_max (str, optional): 下单时间范围最大值 (YYYY-MM-DD HH:MM:SS)。
        is_signed (bool, optional): 是否已签收 (True: 已签收, False: 未签收)。
        is_settled (bool, optional): 是否已结算 (True: 已结算, False: 未结算)。
        
    Returns:
        dict: 包含符合条件的订单列表，每个订单包含其明细和商品信息。
    """
    conn = None
    orders_data = defaultdict(lambda: {"order_info": {}, "order_details": []})
    result_list = []

    try:
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        # 构建基础查询语句
        query = """
            SELECT
                o.order_id,
                o.purchaser,
                o.total_amount,
                o.order_time,
                o.delivery_time,
                o.is_settled,
                o.settlement_time,
                o.expected_delivery_time,
                od.detail_id,
                od.quantity AS ordered_quantity,
                od.price AS ordered_price,
                od.amount AS ordered_amount,
                p.product_id,
                p.product_name,
                p.product_type,
                p.specification,
                p.price AS current_product_price,
                p.stock_quantity,
                p.description
            FROM
                orders AS o
           
 JOIN                order_details AS od ON o.order_id = od.order_id
            JOIN
                products AS p ON od.product_id = p.product_id
            WHERE
                o.purchaser = %s
        """
        params = [purchaser]

        # 添加可选查询条件
        if order_time_min:
            query += " AND o.order_time >= %s"
            params.append(order_time_min)
        if order_time_max:
            query += " AND o.order_time <= %s"
            params.append(order_time_max)
        
        if is_signed is not None:
            if is_signed:
                query += " AND o.delivery_time IS NOT NULL"
            else:
                query += " AND o.delivery_time IS NULL"
        
        if is_settled is not None:
            if is_settled:
                query += " AND o.is_settled = 1"
            else:
                query += " AND o.is_settled = 0"

        query += " ORDER BY o.order_time DESC, od.detail_id ASC;"

        cursor.execute(query, tuple(params))
        rows = cursor.fetchall()

        if not rows:
            return {"orders": []}

        for row in rows:
            order_id = row['order_id']

            # Populate order_info only once per order_id
            if not orders_data[order_id]["order_info"]:
                orders_data[order_id]["order_info"] = {
                    "order_id": row['order_id'],
                    "purchaser": row['purchaser'],
                    "total_amount": row['total_amount'],
                    "order_time": row['order_time'],
                    "delivery_time": row['delivery_time'],
                    "is_settled": bool(row['is_settled']),
                    "settlement_time": row['settlement_time'],
                    "expected_delivery_time": row['expected_delivery_time']
                }

            # Populate product_info for each order detail
            product_info = {
                "product_id": row['product_id'],
                "product_name": row['product_name'],
                "product_type": row['product_type'],
                "specification": row['specification'],
                "current_price": row['current_product_price'],
                "stock_quantity": row['stock_quantity'],
                "description": row['description']
            }

            # Populate order_detail
            order_detail = {
                "detail_id": row['detail_id'],
                "quantity": row['ordered_quantity'],
                "price_at_order": row['ordered_price'],
                "amount": row['ordered_amount'],
                "product_info": product_info # Include full product info
            }
            orders_data[order_id]["order_details"].append(order_detail)
        
        # Convert defaultdict to a list of dicts for final output
        for order_key in sorted(orders_data.keys(), reverse=True): # Newest orders first
            result_list.append(orders_data[order_key])

    except mysql.connector.Error as e:
        print(f"Database error during order query: {e}")
        return {"error": f"Database error: {e}"}
    except Exception as e:
        print(f"An unexpected error occurred during order query: {e}")
        return {"error": f"Unexpected error: {e}"}
    finally:
        if conn:
            conn.close()

    return {"orders": result_list}